﻿//2009.12.17 By Todd
//2010-04-15 新增QueryWeb_SiteErrType 查詢故障代碼 by May

//2010-04-19 新增查詢Web_PreAndetail Table資料(所有，或是傳時間區間)        by May
//           新增查詢Web_PreAndetail Table累計流量(所有，或是傳時間區間) 

//2010-04-21 新增計算節省電費api(所有，或是傳時間區間)  by May
//2010-05-11 從Web_PreAndetail Table查詢時，設定時間區間是QueryEndDateTime    by May
//2010-05-28 修正ComputeSaveMoney_byObjNo api，從Web_PreAnDetail取值從tagname改為objno    by May

//2010-06-02 新增GetAIRawData_Time_byObjNo，取值從tagname改為objno   by May
//2010-06-07 by May
//1.優化ComputeSaveMoney_byObjNo(需傳時間參數)與ComputeSaveMoney(需傳時間參數)，起始時間直接設定
//2.新增計算躉購電費api(所有，或是傳時間區間)
//3.新增計算二氧化碳減排量api(所有，或是傳時間區間)
//2010-06-08 新增計算對應森林面積api(所有，或是傳時間區間)  by May
//2010-06-09 更新所有從Web_PreAnDetail取值的函式，傳入時間區間時改查詢StartDateTime。即開始時間<=StartDateTime<結束時間 by May
//2010-07-01 by May
//1.新增QueryRowdataTableName，查詢原始資料Table名稱
//2.新增QueryMeterAcc_Time_byObjNo查詢錶類ObjNo 兩段日期時間區間內累計統計，直接由結束時間的讀值減去開始時間的讀值
//2010-07-02 新增GetAINotZeroRawData_Time_byObjNo 取得不為零的原始資料 by May
//2010-07-03 新增GetAIRawDataTop_byObjNo取區間內AI原始資料第一筆，依照UpdateTime Sort順序可變換
//2010-12-07 update by May
//1.新增GetCurrentFlowHr_Time傳入開始、結束時間，GrpNo, SiteID, 回傳每個小時的瞬間流量資料
//2.新增GetCurrentFlow_Time傳入開始、結束時間，GrpNo, SiteID, 回傳瞬間流量資料
//2010-12-09 update by May
//1.新增GetCurrentFlow_Time(string strGrpNo, string SiteID) // GrpNo, SiteID, 回傳最新一筆瞬間流量資料
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class ReportAPI
    {
        private string strSCADADSN = System.Configuration.ConfigurationManager.AppSettings["strDSN"];
        string strSQL;

        //功能說明：API自用函示
        //Edit  by  Victor
        #region public DataTable GenDataTable(string strSQL)       //產生DataTable
        public DataTable GenDataTable(string strSQL)
        {
            try
            {
                SqlConnection conn = new SqlConnection();               //MS SQL
                conn.ConnectionString = strSCADADSN;

                DataTable myDataTable = new DataTable();
                SqlDataAdapter QDataAdapter = new SqlDataAdapter(strSQL, conn);
                QDataAdapter.Fill(myDataTable);

                QDataAdapter.Dispose();
                conn.Close();
                return myDataTable;
            }
            catch
            {

                return null;
            }
        }
        #endregion

        #region public bool ExeSQL(string strSQL)      //執行SQL語法，傳回執行完成與否
        public bool ExeSQL(string strSQL)
        {
            bool bFlag = true;

            try
            {
                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }

        public bool ExeSQL(string strSQL, int intTimeOut)
        {
            bool bFlag = true;

            try
            {

                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.CommandTimeout = intTimeOut;
                cmd.ExecuteNonQuery();
                conn.Close();

            }
            catch (Exception ex)
            {
                AddErrorMsg("ExeSQL", ex.ToString(), strSQL);
                bFlag = false;
            }

            return bFlag;
        }
        #endregion

        #region public bool AddErrorMsg(string ClassName, string MethodName, string ErrorMsg, string SQLCommand)       //新增錯誤訊息
        /// <summary>
        /// 新增錯誤訊息
        /// </summary>
        /// <param name="MethodName">函示名稱</param>
        /// <param name="ErrorMsg">錯誤訊息</param>
        /// <param name="SQLCommand">SQL 指令</param>
        /// <returns>回傳寫入成功與否</returns>
        public bool AddErrorMsg(string MethodName, string ErrorMsg, string SQLCommand)
        {
            try
            {
                string ClassName = "IENClass.CYConfig";
                string strSQL = "Insert into Web_ErrorMsg(ClassName, MethodName, ErrorMsg, SQLCommand) Values(@ClassName, @MethodName, @ErrorMsg, @SQLCommand) ";

                SqlConnection conn = new SqlConnection(strSCADADSN);
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, conn);

                cmd.Parameters.AddWithValue("@ClassName", ClassName);
                cmd.Parameters.AddWithValue("@MethodName", MethodName);
                cmd.Parameters.AddWithValue("@ErrorMsg", ErrorMsg);
                cmd.Parameters.AddWithValue("@SQLCommand", SQLCommand);
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #region public DataTable GetTagConfig()
        public DataTable GetTagConfig(string SiteID, string GrpNo)
        {
            try
            {
                strSQL = "Select TagName, strUnit From TagConfig_cfg Where SiteID = '" + SiteID + "' and GrpNo = '" + GrpNo + "' Order By ObjNo";
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region public DataTable GetAIRawData_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strAI) //取得AI點的原始資料
        public DataTable GetAIRawData_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strAI, string SiteID)
        {
            string strTableType = "", strTable = "";
            strTableType = "DayRptAI";

            int i = 0;
            string strYM = startDateTime.ToString("yyyyMM");
            string strSQLTable = "";
            try
            {
                while (strYM.CompareTo(endDateTime.ToString("yyyyMM")) <= 0)
                {
                    strTable = strTableType + strYM;
                    strSQLTable = "Select Count(*) As Count From  sysobjects where name= '" + strTable + "'";
                    DataTable dtTable = GenDataTable(strSQLTable);
                    if (dtTable != null)
                    {
                        if (dtTable.Rows.Count > 0)
                        {
                            if (dtTable.Rows[0]["Count"].ToString() == "1")
                            {
                                if (i == 0)
                                {
                                    strSQL = " (Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + " sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And TagName='" + strAI + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                    i = i + 1;
                                }
                                else
                                {
                                    strSQL = strSQL + " Union ";
                                    strSQL = strSQL + "( Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + "  sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And TagName='" + strAI + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                }
                            }
                        }
                    }

                    strYM = DateTime.Parse(strYM.Substring(0, 4) + "/" + strYM.Substring(4, 2) + "/01").AddMonths(1).ToString("yyyyMM");
                }

                if (i > 0)
                {
                    strSQL = strSQL + " Order By UpdateTime";
                }
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion


        #region public DataTable GetAIRawData_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string SiteID) //取得AI點的原始資料
        public DataTable GetAIRawData_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string SiteID)
        {
            string strTableType = "", strTable = "";
            strTableType = "DayRptAI";

            int i = 0;
            string strYM = startDateTime.ToString("yyyyMM");
            string strSQLTable = "";
            try
            {
                while (strYM.CompareTo(endDateTime.ToString("yyyyMM")) <= 0)
                {
                    strTable = strTableType + strYM;
                    strSQLTable = "Select Count(*) As Count From  sysobjects where name= '" + strTable + "'";
                    DataTable dtTable = GenDataTable(strSQLTable);
                    if (dtTable != null)
                    {
                        if (dtTable.Rows.Count > 0)
                        {
                            if (dtTable.Rows[0]["Count"].ToString() == "1")
                            {
                                if (i == 0)
                                {
                                    strSQL = " (Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + " sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And ObjNo='" + strObjNo + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                    i = i + 1;
                                }
                                else
                                {
                                    strSQL = strSQL + " Union ";
                                    strSQL = strSQL + "( Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + "  sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And ObjNo='" + strObjNo + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                }
                            }
                        }
                    }

                    strYM = DateTime.Parse(strYM.Substring(0, 4) + "/" + strYM.Substring(4, 2) + "/01").AddMonths(1).ToString("yyyyMM");
                }

                if (i > 0)
                {
                    strSQL = strSQL + " Order By UpdateTime";
                }
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion
        #region public  GetSiteMaintain_Time(DateTime startDateTime, DateTime endDateTime, string strSiteID)//設起始時間，查維護資料
        public DataTable GetSiteMaintain_Time(DateTime startDateTime, DateTime endDateTime, string strSiteID)
        {
            string strSQL = "";
            strSQL += "select * from Web_SiteMaintain";
            strSQL += " where SiteID='" + strSiteID + "'";
            strSQL += " and (InfoDate >='" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "' and InfoDate<='" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "') order by InfoDate";
            try
            {
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }

        }
        #endregion

        //Edit  by  May
        #region DataTable QueryWeb_SiteErrType    //查詢故障代碼
        public DataTable QueryWeb_SiteErrType()
        {
            try
            {
                strSQL = "Select * From Web_SiteErrType";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryWeb_SiteErrTypef", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion
        //Edit  by  May



        //Edit by May 2010-04-19
        //查詢錶類TagName 兩段日期時間區間內累計統計 相關Table : Web_PreAnDetail  
        #region public DataTable QueryMeterDateTimePeriod(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        public DataTable QueryMeterDateTimePeriod(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                strSQL = " Select  Isnull(Sum(diffValue),0) As Value From Web_PreAnDetail Where StartDateTime >='" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "' and StartDateTime<'" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL = strSQL + " And TagName='" + strTagName + "' And GrpNo = '" + strGrpNo + "' And SiteID='" + strSiteID + "'";

                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢錶類TagName所有累計統計 相關Table : Web_PreAnDetail  
        #region public DataTable QueryMeterDateTimePeriod(string strGrpNo, string strTagName, string strSiteID)
        public DataTable QueryMeterDateTimePeriod(string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                strSQL = " Select  Isnull(Sum(diffValue),0) As Value";
                strSQL = strSQL + " From Web_PreAnDetail Where TagName='" + strTagName + "' And GrpNo = '" + strGrpNo + "' And SiteID='" + strSiteID + "'";

                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢錶類TagName所有節省電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaveMoney(string strGrpNo, string strTagName, string strSiteID)
        public float ComputeSaveMoney(string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //找出Web_PreAnDetail，第一筆和最後一筆資料，找出費率開始與結束時間
                DataTable dtAcc = new ReportAPI().QueryPreAnDetail_Time(strGrpNo, strTagName, strSiteID);
                DateTime computeStart = Convert.ToDateTime(dtAcc.Rows[0]["StartDateTime"]);
                DateTime computeEnd = Convert.ToDateTime(dtAcc.Rows[dtAcc.Rows.Count - 1]["EndDateTime"]);

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        DataTable dtAccTable = new ReportAPI().QueryMeterDateTimePeriod(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strTagName, strSiteID);
                        float dAcc = float.Parse(dtAccTable.Rows[0]["Value"].ToString());
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類TagName時間區間內節省電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaveMoney(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        public float ComputeSaveMoney(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //找出Web_PreAnDetail，設定費率開始與結束時間
                DateTime computeStart = startDateTime;
                DateTime computeEnd = endDateTime;

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        DataTable dtAccTable = new ReportAPI().QueryMeterDateTimePeriod(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strTagName, strSiteID);
                        float dAcc = float.Parse(dtAccTable.Rows[0]["Value"].ToString());
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion


        //查詢時間區間內Web_PreAndetail Table，以startdatetime欄位為主，以DateTime格式濾出  相關Table：Web_PreAnDetail
        #region public DataTable QueryPreAnDetail_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        public DataTable QueryPreAnDetail_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                strSQL = "Select * From Web_PreAnDetail Where StartDateTime >='" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "' and StartDateTime<'" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL += " and GrpNo = '" + strGrpNo + "' and TagName = '" + strTagName + "' and SiteID='" + strSiteID + "' order By StartDateTime";
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢Web_PreAndetail Table所有資料，以startdatetime欄位為主，以DateTime格式濾出  相關Table：Web_PreAnDetail
        #region public DataTable QueryPreAnDetail_Time(string strGrpNo, string strTagName, string strSiteID)
        public DataTable QueryPreAnDetail_Time(string strGrpNo, string strTagName, string strSiteID)
        {
            try
            {
                strSQL = "Select * From Web_PreAnDetail";
                strSQL += " where GrpNo = '" + strGrpNo + "' and TagName = '" + strTagName + "' and SiteID='" + strSiteID + "' order By EndDateTime";
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion
        //Edit by May 2010-04-19

        //Edit by May 2010-05-06
        #region public DataTable GetAIRawData_Hr(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID) //取得AI點的原始資料
        public DataTable GetAIRawData_Hr(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID)
        {
            string strTableType = "", strTable = "";
            strTableType = "DayRptAI";

            int i = 0;
            string strYM = startDateTime.ToString("yyyyMM");
            string strSQLTable = "";
            try
            {
                while (strYM.CompareTo(endDateTime.ToString("yyyyMM")) <= 0)
                {
                    strTable = strTableType + strYM;
                    strSQLTable = "Select Count(*) As Count From  sysobjects where name= '" + strTable + "'";
                    DataTable dtTable = GenDataTable(strSQLTable);
                    if (dtTable != null)
                    {
                        if (dtTable.Rows.Count > 0)
                        {
                            if (dtTable.Rows[0]["Count"].ToString() == "1")
                            {
                                if (i == 0)
                                {
                                    strSQL = " (Select SiteID, GrpNo, ObjNo, TagName, Substring(Convert(Char(13), UpdateTime, 120), 1, 13) as DayHour,";
                                    strSQL = strSQL + " avg(convert(float, sngNow)) as SngHrAvg From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " Group by SiteID, GrpNo, ObjNo, TagName, Substring(Convert(Char(13), UpdateTime, 120), 1, 13))";
                                    i = i + 1;
                                }
                                else
                                {
                                    strSQL = strSQL + " Union ";
                                    strSQL = strSQL + " (Select SiteID, GrpNo, ObjNo, TagName, Substring(Convert(Char(13), UpdateTime, 120), 1, 13) as DayHour,";
                                    strSQL = strSQL + " avg(convert(float, sngNow)) as SngHrAvg From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " Group by SiteID, GrpNo, ObjNo, TagName, Substring(Convert(Char(13), UpdateTime, 120), 1, 13))";

                                }
                            }
                        }
                    }
                    strYM = DateTime.Parse(strYM.Substring(0, 4) + "/" + strYM.Substring(4, 2) + "/01").AddMonths(1).ToString("yyyyMM");
                }
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion


        //Edit by May 2010-05-11
        //查詢錶類ObjNo 兩段日期時間區間內累計統計 相關Table : Web_PreAnDetail  
        #region public DataTable QueryMeterDateTimePeriod_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public DataTable QueryMeterDateTimePeriod_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                strSQL = " Select  Isnull(Sum(diffValue),0) As Value From Web_PreAnDetail Where StartDateTime >='" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "' and StartDateTime<'" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL = strSQL + " And ObjNo='" + strObjNo + "' And GrpNo = '" + strGrpNo + "' And SiteID='" + strSiteID + "'";

                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢錶類ObjNo所有累計統計 相關Table : Web_PreAnDetail  
        #region public DataTable QueryMeterDateTimePeriod_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public DataTable QueryMeterDateTimePeriod_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                strSQL = " Select  Isnull(Sum(diffValue),0) As Value";
                strSQL = strSQL + " From Web_PreAnDetail Where ObjNo='" + strObjNo + "' And GrpNo = '" + strGrpNo + "' And SiteID='" + strSiteID + "'";

                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢錶類ObjNo所有節省電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaveMoney_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeSaveMoney_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable();
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定查詢區間，原始資料Table第一筆，到最後一筆。
                DateTime computeStart = new DateTime();
                DataTable dtFirstTable = new ReportAPI().QueryRowdataTableName();
                if (dtFirstTable != null && dtFirstTable.Rows.Count > 0)
                {
                    string strStartTime = dtFirstTable.Rows[0]["name"].ToString().Substring(8, 4) + "/" + dtFirstTable.Rows[0]["name"].ToString().Substring(12, 2) + "/01";
                    computeStart = DateTime.Parse(strStartTime);
                }
                DateTime computeEnd = DateTime.Now;

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;


                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類ObjNo時間區間內節省電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaveMoney_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeSaveMoney_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定費率開始與結束時間
                DateTime computeStart = startDateTime;
                DateTime computeEnd = endDateTime;

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["Tariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["Tariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;

                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion


        //查詢時間區間內Web_PreAndetail Table，以startdatetime欄位為主，以DateTime格式濾出  相關Table：Web_PreAnDetail
        #region public DataTable QueryPreAnDetail_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public DataTable QueryPreAnDetail_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                strSQL = "Select * From Web_PreAnDetail Where StartDateTime >='" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "' and StartDateTime<'" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL += " and GrpNo = '" + strGrpNo + "' and ObjNo = '" + strObjNo + "' and SiteID='" + strSiteID + "' order By EndDateTime";
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        //查詢Web_PreAndetail Table所有資料，以startdatetime欄位為主，以DateTime格式濾出  相關Table：Web_PreAnDetail
        #region public DataTable QueryPreAnDetail_Time_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public DataTable QueryPreAnDetail_Time_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                strSQL = "Select * From Web_PreAnDetail";
                strSQL += " where GrpNo = '" + strGrpNo + "' and ObjNo = '" + strObjNo + "' and SiteID='" + strSiteID + "' order By EndDateTime";
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion
        //Edit by May 2010-05-11

        //Edit by May 2010-06-07
        //查詢錶類ObjNo所有躉購電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaleMoney_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeSaleMoney_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable();
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));


                //設定查詢區間，原始資料Table第一筆，到最後一筆。
                DateTime computeStart = new DateTime();
                DataTable dtFirstTable = new ReportAPI().QueryRowdataTableName();
                if (dtFirstTable != null && dtFirstTable.Rows.Count > 0)
                {
                    string strStartTime = dtFirstTable.Rows[0]["name"].ToString().Substring(8, 4) + "/" + dtFirstTable.Rows[0]["name"].ToString().Substring(12, 2) + "/01";
                    computeStart = DateTime.Parse(strStartTime);
                }
                DateTime computeEnd = DateTime.Now;

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteSaleTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["SaleTariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["SaleTariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["SaleTariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["SaleTariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;

                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類ObjNo時間區間內躉購電費 相關Table : Web_PreAnDetail  
        #region public float ComputeSaleMoney_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeSaleMoney_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定費率開始與結束時間
                DateTime computeStart = startDateTime;
                DateTime computeEnd = endDateTime;

                //filter出屬於這個siteID的費率設定
                DataTable dtTariff = new AccessSite().QueryWeb_SiteTariff();
                dtTariff.DefaultView.RowFilter = "SiteID='" + strSiteID + "'";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "StartDate<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["SaleTariff"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["SaleTariff"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "StartDate>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]) > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["StartDate"]);

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["StartDate"]);

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["StartDate"]);
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["SaleTariff"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["SaleTariff"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;

                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類ObjNo所有二氧化碳減排量 相關Table : Web_PreAnDetail  
        #region public float ComputeCo2_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeCo2_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable();
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定查詢區間，原始資料Table第一筆，到最後一筆。
                DateTime computeStart = new DateTime();
                DataTable dtFirstTable = new ReportAPI().QueryRowdataTableName();
                if (dtFirstTable != null && dtFirstTable.Rows.Count > 0)
                {
                    string strStartTime = dtFirstTable.Rows[0]["name"].ToString().Substring(8, 4) + "/" + dtFirstTable.Rows[0]["name"].ToString().Substring(12, 2) + "/01";
                    computeStart = DateTime.Parse(strStartTime);
                }
                DateTime computeEnd = DateTime.Now;

                //filter 二氧化碳減排量的Web_KeyDefinitionConfig設定
                DataTable dtTariff = new AccessSite().QueryWeb_KeyDefinitionConfig();
                dtTariff.DefaultView.RowFilter = "Namespace='BillEmission'";
                dtTariff.DefaultView.Sort = "Item ASC";
                dtTariff = dtTariff.DefaultView.ToTable();
                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "Item<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "Item>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01") > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01");

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["Item"] + "/01/01");

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["Item"] + "/01/01");
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;

                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類ObjNo時間區間內二氧化碳減排量 相關Table : Web_PreAnDetail  
        #region public float ComputeCo2_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeCo2_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定費率開始與結束時間
                DateTime computeStart = startDateTime;
                DateTime computeEnd = endDateTime;

                //filter 二氧化碳減排量的Web_KeyDefinitionConfig設定
                DataTable dtTariff = new AccessSite().QueryWeb_KeyDefinitionConfig();
                dtTariff.DefaultView.RowFilter = "Namespace='BillEmission'";
                dtTariff.DefaultView.Sort = "Item ASC";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "Item<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "Item>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01") > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01");

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["Item"] + "/01/01");

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["Item"] + "/01/01");
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dMoney = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        float dAcc = 0;


                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccMoney = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString());
                        dMoney += dAccMoney;
                    }
                    return dMoney;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion
        //Edit by May 2010-06-07

        //Edit by May 2010-06-08
        //查詢錶類ObjNo所有對應森林面積 相關Table : Web_PreAnDetail  
        #region public float ComputeForest_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeForest_byObjNo(string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable();
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定查詢區間，原始資料Table第一筆，到最後一筆。
                DateTime computeStart = new DateTime();
                DataTable dtFirstTable = new ReportAPI().QueryRowdataTableName();
                if (dtFirstTable != null && dtFirstTable.Rows.Count > 0)
                {
                    string strStartTime = dtFirstTable.Rows[0]["name"].ToString().Substring(8, 4) + "/" + dtFirstTable.Rows[0]["name"].ToString().Substring(12, 2) + "/01";
                    computeStart = DateTime.Parse(strStartTime);
                }
                DateTime computeEnd = DateTime.Now;


                //filter 二氧化碳減排量的Web_KeyDefinitionConfig設定
                DataTable dtTariff = new AccessSite().QueryWeb_KeyDefinitionConfig();
                dtTariff.DefaultView.RowFilter = "Namespace='BillEmission'";
                dtTariff.DefaultView.Sort = "Item ASC";
                dtTariff = dtTariff.DefaultView.ToTable();
                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "Item<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "Item>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01") > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01");

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["Item"] + "/01/01");

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["Item"] + "/01/01");
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }

                    float dForest = 0;
                    float dAcc = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        dAcc = 0;

                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccForest = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString()) / 1000 / 16 * 10000;
                        dForest += dAccForest;
                    }
                    return dForest;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion

        //查詢錶類ObjNo時間區間內對應森林面積 相關Table : Web_PreAnDetail  
        #region public float ComputeForest_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public float ComputeForest_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                //產生計算費率的Table
                DataTable dtReturn = new DataTable(); ;
                dtReturn.Columns.Add(new DataColumn("Start", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("End", System.Type.GetType("System.DateTime")));
                dtReturn.Columns.Add(new DataColumn("Tariff", System.Type.GetType("System.Double")));

                //設定費率開始與結束時間
                DateTime computeStart = startDateTime;
                DateTime computeEnd = endDateTime;

                //filter 二氧化碳減排量的Web_KeyDefinitionConfig設定
                DataTable dtTariff = new AccessSite().QueryWeb_KeyDefinitionConfig();
                dtTariff.DefaultView.RowFilter = "Namespace='BillEmission'";
                dtTariff.DefaultView.Sort = "Item ASC";
                dtTariff = dtTariff.DefaultView.ToTable();

                if (dtTariff == null || dtTariff.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    dtTariff.DefaultView.RowFilter = "Item<'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    DataTable dtFilterTariff = dtTariff.DefaultView.ToTable();
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[dtFilterTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }
                    else
                    {
                        DataRow row = dtReturn.NewRow();
                        row["Start"] = computeStart;
                        row["End"] = computeEnd;
                        row["Tariff"] = Convert.ToDouble(dtTariff.Rows[dtTariff.Rows.Count - 1]["ItemValue"]);
                        dtReturn.Rows.Add(row);
                    }

                    dtTariff.DefaultView.RowFilter = "Item>'" + computeStart.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                    dtFilterTariff = dtTariff.DefaultView.ToTable();

                    DateTime dtStart = DateTime.Now;
                    DateTime dtEnd = DateTime.Now;
                    if (dtFilterTariff != null && dtFilterTariff.Rows.Count > 0)
                    {
                        if (Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01") > computeEnd)
                        {
                            dtReturn.Rows[0]["End"] = computeEnd;
                        }
                        else
                        {
                            dtReturn.Rows[0]["End"] = Convert.ToDateTime(dtFilterTariff.Rows[0]["Item"] + "/01/01");

                            for (int i = 0; i < dtFilterTariff.Rows.Count; i++)
                            {
                                dtStart = Convert.ToDateTime(dtFilterTariff.Rows[i]["Item"] + "/01/01");

                                if (i + 1 <= dtFilterTariff.Rows.Count - 1)
                                {
                                    dtEnd = Convert.ToDateTime(dtFilterTariff.Rows[i + 1]["Item"] + "/01/01");
                                }
                                else
                                {
                                    dtEnd = DateTime.Now;
                                }

                                if (dtEnd > computeEnd)
                                {
                                    dtEnd = computeEnd;
                                    DataRow row = dtReturn.NewRow();
                                    row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                    break;
                                }
                                else
                                {
                                    DataRow row = dtReturn.NewRow();
                                    row["Start"] = dtStart;
                                    row["End"] = dtEnd;
                                    row["Tariff"] = Convert.ToDouble(dtFilterTariff.Rows[i]["ItemValue"]);
                                    dtReturn.Rows.Add(row);
                                }

                            }
                        }

                    }
                    else
                    {
                        dtReturn.Rows[0]["End"] = computeEnd;
                    }
                    float dForest = 0;
                    float dAcc = 0;
                    bool isAccFirstData = false;
                    for (int i = 0; i < dtReturn.Rows.Count; i++)
                    {
                        dAcc = 0;
                        if (isAccFirstData == false)
                        {
                            DataTable dtRawData = new ReportAPI().GetAIRawData_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                            if (dtRawData != null && dtRawData.Rows.Count > 0)
                            {
                                dAcc = float.Parse(dtRawData.Rows[dtRawData.Rows.Count - 1]["sngNow"].ToString());
                                isAccFirstData = true;
                            }
                        }
                        else
                        {
                            dAcc = new ReportAPI().QueryMeterDateAcc_Time_byObjNo(Convert.ToDateTime(dtReturn.Rows[i]["Start"]), Convert.ToDateTime(dtReturn.Rows[i]["End"]), strGrpNo, strObjNo, strSiteID);
                        }
                        float dAccForest = dAcc * float.Parse(dtReturn.Rows[i]["Tariff"].ToString()) / 1000 / 16 * 10000;
                        dForest += dAccForest;
                    }
                    return dForest;
                }
            }
            catch
            {
                return 0;
            }
        }
        #endregion
        //Edit by May 2010-06-08


        //Edit by May 2010-07-01
        //查詢原始資料Table名稱
        #region DataTable QueryRowdataTableName   //查詢原始資料Table名稱
        public DataTable QueryRowdataTableName()
        {
            try
            {
                strSQL = "Select * From sysobjects where name like 'DayRptAI[0-9][0-9][0-9][0-9][0-9][0-9]' order by name";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QuerySysobjects", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion


        //查詢錶類ObjNo 兩段日期時間區間內累計統計 
        #region public DataTable QueryMeterAcc_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        public float QueryMeterDateAcc_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string strSiteID)
        {
            try
            {
                DataTable dtRowdata = new ReportAPI().GetAIRawData_Time_byObjNo(startDateTime, endDateTime, strGrpNo, strObjNo, strSiteID);
                float dStartRecord = float.Parse(dtRowdata.Rows[0]["sngNow"].ToString());
                float dEndRecord = float.Parse(dtRowdata.Rows[dtRowdata.Rows.Count - 1]["sngNow"].ToString());
                float dDiff = dEndRecord - dStartRecord;
                return dDiff;
            }
            catch
            {
                return 0;
            }
        }
        #endregion
        //Edit by May 2010-07-01

        //Edit by May 2010-07-02
        //查詢錶類ObjNo讀值不為0的原始資料
        #region public DataTable GetAINotZeroRawData_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string SiteID) //取得AI點的原始資料
        public DataTable GetAINotZeroRawData_Time_byObjNo(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string strObjNo, string SiteID)
        {
            string strTableType = "", strTable = "";
            strTableType = "DayRptAI";

            int i = 0;
            string strYM = startDateTime.ToString("yyyyMM");
            string strSQLTable = "";
            try
            {
                while (strYM.CompareTo(endDateTime.ToString("yyyyMM")) <= 0)
                {
                    strTable = strTableType + strYM;
                    strSQLTable = "Select Count(*) As Count From  sysobjects where name= '" + strTable + "'";
                    DataTable dtTable = GenDataTable(strSQLTable);
                    if (dtTable != null)
                    {
                        if (dtTable.Rows.Count > 0)
                        {
                            if (dtTable.Rows[0]["Count"].ToString() == "1")
                            {
                                if (i == 0)
                                {
                                    strSQL = " (Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + " sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And ObjNo='" + strObjNo + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And Convert(float, sngNow)>0";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                    i = i + 1;
                                }
                                else
                                {
                                    strSQL = strSQL + " Union ";
                                    strSQL = strSQL + "( Select Substring(Convert(Char(10), UpdateTime, 111) + ' ' + Convert(Char(8), updatetime, 108),1,16) As UpdateTime, ";
                                    strSQL = strSQL + "  sngNow From " + strTable;
                                    strSQL = strSQL + " Where GrpNo = '" + strGrpNo + "' And ObjNo='" + strObjNo + "' and SiteID = '" + SiteID + "'";
                                    strSQL = strSQL + " And Convert(float, sngNow)>0";
                                    strSQL = strSQL + " And updateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    strSQL = strSQL + " And updateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "')";
                                }
                            }
                        }
                    }

                    strYM = DateTime.Parse(strYM.Substring(0, 4) + "/" + strYM.Substring(4, 2) + "/01").AddMonths(1).ToString("yyyyMM");
                }

                if (i > 0)
                {
                    strSQL = strSQL + " Order By UpdateTime";
                }
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion
        //Edit by May 2010-07-02


        //Edit by May 2010-07-03
        //取區間內AI原始資料第一筆，依照UpdateTime Sort順序可變換__時間單位到日
        #region public DataTable GetAIRawDataTop_byObjNo(DateTime startDay, DateTime endDay, string strGrpNo, string strObjNo, string strSiteID, string strSort)
        public DataTable GetAIRawDataTop_byObjNo(DateTime startDay, DateTime endDay, string strGrpNo, string strObjNo, string strSiteID, string strSort)
        {
            int i = 0;
            string strYM = startDay.ToString("yyyyMM");
            string endYM = endDay.ToString("yyyyMM");
            string strSQLTable = "", strSQLTable_a = "", strTableType = "";

            strTableType = "DayRptAI";

            try
            {
                while (strYM.CompareTo(endYM) <= 0)
                {
                    //Todd--------
                    strSQL = "Select Count(*) As Count From  sysobjects where name= '" + strTableType + strYM + "'";
                    DataTable dtTable = GenDataTable(strSQL);
                    if (dtTable != null)
                    {
                        if (dtTable.Rows[0]["Count"].ToString() == "1")//擋MSSQL
                        {
                            if (i == 0)
                            {
                                strSQLTable = "( Select * From  " + strTableType + strYM;
                                i = i + 1;
                            }
                            else
                                strSQLTable = strSQLTable + " Union Select * From " + strTableType + strYM;
                        }
                    }

                    strYM = DateTime.Parse(strYM.Substring(0, 4) + "/" + strYM.Substring(4, 2) + "/01").AddMonths(1).ToString("yyyyMM");
                }

                if (strSQLTable != "")
                {
                    strSQLTable_a = strSQLTable + " ) a";
                }

                strSQL = "Select Top 1 * From " + strSQLTable_a;
                strSQL = strSQL + " Where updateTime >= '" + startDay.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL = strSQL + " And updateTime  < '" + endDay.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL = strSQL + " And GrpNo = '" + strGrpNo + "' and ObjNo = '" + strObjNo + "' and SiteID='" + strSiteID + "' Order by UpdateTime " + strSort;
                return GenDataTable(strSQL);
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region public DataTable GetCurrentFlowHr_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID) // 傳入開始、結束時間，GrpNo, SiteID, 回傳每個小時的瞬間流量資料
        //Edit by May 2010-12-07
        //傳入開始、結束時間，GrpNo, SiteID, 回傳每個小時的瞬間流量資料
        /// <summary>
        /// 傳入開始、結束時間，GrpNo, SiteID, 回傳每個小時的瞬間流量資料
        /// </summary>
        /// <param name="startDateTime">開始時間</param>
        /// <param name="endDateTime">結束時間</param>
        /// <param name="strGrpNo">GrpNo</param>
        /// <param name="SiteID">SiteID</param>
        /// <returns>每個小時的瞬間流量資料</returns>
        public DataTable GetCurrentFlowHr_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID)
        {
            try
            {
                strSQL = "select Substring(Convert(varchar,EndDateTime, 120), 1, 11) as Day, Substring(Convert(varchar,EndDateTime, 120), 12, 2) as Hr, Avg(DiffValue/DATEDIFF(SECOND, CONVERT(varchar, startDateTime, 120),  CONVERT(varchar, endDateTime, 120))*60*60) as sngNow from Web_PreAnDetail";
                strSQL +=" Where GrpNo = '" + strGrpNo + "' and SiteID = '" + SiteID + "'";
                strSQL +=" And EndDateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL +=" And EndDateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL +=" Group by Substring(Convert(varchar,EndDateTime, 120), 1, 11), Substring(Convert(varchar,EndDateTime, 120), 12, 2)";
                return GenDataTable(strSQL);
            }

            catch
            {
                return null;
            }
        }
        #endregion

        #region public DataTable GetCurrentFlow_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID) // 傳入開始、結束時間，GrpNo, SiteID, 回傳瞬間流量資料
        //Edit by May 2010-12-07
        //傳入開始、結束時間，GrpNo, SiteID, 回傳瞬間流量資料
        /// <summary>
        /// 傳入開始、結束時間，GrpNo, SiteID, 回傳瞬間流量資料
        /// </summary>
        /// <param name="startDateTime">開始時間</param>
        /// <param name="endDateTime">結束時間</param>
        /// <param name="strGrpNo">GrpNo</param>
        /// <param name="SiteID">SiteID</param>
        /// <returns>瞬間流量資料</returns>
        public DataTable GetCurrentFlow_Time(DateTime startDateTime, DateTime endDateTime, string strGrpNo, string SiteID)
        {
            try
            {
                strSQL = "select Substring(Convert(varchar,EndDateTime, 120), 1, 19) as CurTime, Avg(DiffValue/DATEDIFF(SECOND, CONVERT(varchar, startDateTime, 120),  CONVERT(varchar, endDateTime, 120))*60*60) as sngNow from Web_PreAnDetail";
                strSQL += " Where GrpNo = '" + strGrpNo + "' and SiteID = '" + SiteID + "'";
                strSQL += " And EndDateTime >= '" + startDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL += " And EndDateTime  < '" + endDateTime.ToString("yyyy/MM/dd HH:mm:ss") + "'";
                strSQL += " Group by Substring(Convert(varchar,EndDateTime, 120), 1, 19)";
                return GenDataTable(strSQL);
            }

            catch
            {
                return null;
            }
        }
        #endregion

        #region public DataTable GetCurrentFlow_Time(string strGrpNo, string SiteID) // GrpNo, SiteID, 回傳最新一筆瞬間流量資料
        //Edit by May 2010-12-09
        //GrpNo, SiteID, 回傳最新一筆瞬間流量資料
        /// <summary>
        /// 回傳最新一筆瞬間流量資料
        /// </summary>
        /// <param name="strGrpNo">GrpNo</param>
        /// <param name="SiteID">SiteID</param>
        /// <returns>最新一筆瞬間流量資料</returns>
        public DataTable GetCurrentFlow_Time(string strGrpNo, string SiteID)
        {
            try
            {
                strSQL = "select Top 1 Substring(Convert(varchar,EndDateTime, 120), 1, 19) as CurTime, Avg(DiffValue/DATEDIFF(SECOND, CONVERT(varchar, startDateTime, 120),  CONVERT(varchar, endDateTime, 120))*60*60) as sngNow from Web_PreAnDetail";
                strSQL += " Where GrpNo = '" + strGrpNo + "' and SiteID = '" + SiteID + "'";
                strSQL += " Group by Substring(Convert(varchar,EndDateTime, 120), 1, 19)";
                strSQL += " Order by Substring(Convert(varchar,EndDateTime, 120), 1, 19) Desc";
                return GenDataTable(strSQL);
            }

            catch
            {
                return null;
            }
        }
        #endregion

    }
}
